package com.san.metadata.dao;

import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.san.base.dao.BaseManager;
import com.san.common.datasource.JdbcManager;
import com.san.common.utils.CTConversionUtils;
import com.san.common.utils.Constant;
import com.san.metadata.domain.DataSourceDO;
import com.san.metadata.domain.ManageTableDO;
import com.san.metadata.vo.ManageColumnsVO;


@Service
public class ManageMetadataManager extends BaseManager {
	
	private static final Logger logger = LoggerFactory.getLogger(ManageMetadataManager.class);

	/**
	 * 管理元数据建表
	 * 
	 * @param sourceType          源数据类型
	 * @param manageTableDO    管理元数据DO
	 * @param targetDataSourceDO  目标数据源DO
	 * @param manageColumnsVOList 管理元数据字段信息List
	 * @return
	 */
	public boolean saveTable(int sourceType, ManageTableDO manageTableDO, DataSourceDO targetDataSourceDO,
			List<ManageColumnsVO> manageColumnsVOList) {
		JdbcManager jdbcManager = new JdbcManager();
		StringBuffer sql = new StringBuffer();
		StringBuffer sqlComment = new StringBuffer();
		boolean flag = false;
		Object[] obj = null;
		Integer j = 0;
		List<Map<String, Object>> list = null;
		DruidPooledConnection conn = null;
		try {
			conn = this.getDBConnection(targetDataSourceDO.getSourceCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		switch (targetDataSourceDO.getSourceType()) {
		case Constant.SOURCE_TYPE_ORACLE: { // 目标数据源 为 ORACLE

			list = jdbcManager.executeQuery(conn, "select * from DBA_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_OMS_TABLE'",
					obj);
			if (list.size() <= 0) { // 创建序列
				String seqSql = "CREATE  SEQUENCE SEQ_OMS_TABLE\r\n" + "minvalue 1\r\n"
						+ "maxvalue 999999999999999999999999\r\n" + "start with 1\r\n" + "increment by 1\r\n"
						+ "nocache";
				jdbcManager.executeSQL(conn, seqSql, obj);
			}

			if (sourceType == Constant.SOURCE_TYPE_ORACLE) { // 源数据源 为 ORACLE
				sql.append("create table " + manageTableDO.getTargetTableName() + "" + "(");
				sqlComment.append("");
				for (ManageColumnsVO i : manageColumnsVOList) {
					j++;
					sql.append(" " + i.getColumnName() + " " // 字段名
							+ CTConversionUtils.Complete(i.getColumnType(), i.getColumnLength(), i.getColumnPrecision(),
									i.getColumnScale()) // 字段类型
							+ " " + ("YES".equals(i.getColumnIsNullable()) ? " NULL " : " NOT NULL ") // 是否为空
							+ (j < manageColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") " + "\n\r");
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
				if (flag) {
					if (manageTableDO.getTargetTableComment() != null
							&& !"".equals(manageTableDO.getTargetTableComment())) {
						sql.append("COMMENT ON table ").append(manageTableDO.getTargetTableName()).append(" IS '")
								.append(manageTableDO.getTargetTableComment()).append("' \n\r"); // 表注释
						flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
					}
					for (ManageColumnsVO i : manageColumnsVOList) {
						if (i.getColumnComment() != null && !"".equals(i.getColumnComment()) && flag) {
							sqlComment.append("comment on column ").append(manageTableDO.getTargetTableName())
									.append(".").append(i.getColumnName()).append(" is '").append(i.getColumnComment())
									.append("' \n\r");
							flag = jdbcManager.executeSQL(conn, sqlComment.toString(), obj);
						}
					}
				}
			}
			if (sourceType == Constant.SOURCE_TYPE_MYSQL) { // 源数据源 为 MYSQL

				list = jdbcManager.executeQuery(conn, "select * from DBA_SEQUENCES WHERE SEQUENCE_NAME = 'OMS_SEQ'",
						obj);
				if (list.size() <= 0) {
					String seqSql = "CREATE  SEQUENCE oms_seq\r\n" + "minvalue 1\r\n"
							+ "maxvalue 999999999999999999999999\r\n" + "start with 1\r\n" + "increment by 1\r\n"
							+ "nocache";
					jdbcManager.executeSQL(conn, seqSql, obj);
				}

				sql.append("create table " + manageTableDO.getTargetTableName() + "" + "(");
				sqlComment.append("");
				for (ManageColumnsVO i : manageColumnsVOList) {
					j++;
					sql.append(" ").append(i.getColumnName()).append(" ") // 字段名
							.append(CTConversionUtils.M2O(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale())) // 字段类型
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL " : " NOT NULL ")) // 是否为空
							.append((j < manageColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ").append("\n\r");
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
				if (flag) {
					if (manageTableDO.getTargetTableComment() != null
							&& !"".equals(manageTableDO.getTargetTableComment())) {
						sql.append("COMMENT ON table ").append(manageTableDO.getTargetTableName()).append(" IS '")
								.append(manageTableDO.getTargetTableComment()).append("' \n\r"); // 表注释
						flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
					}
					for (ManageColumnsVO i : manageColumnsVOList) {
						if (i.getColumnComment() != null && !"".equals(i.getColumnComment()) && flag) {
							sqlComment.append("comment on column ").append(manageTableDO.getTargetTableName())
									.append(".").append(i.getColumnName()).append(" is '").append(i.getColumnComment())
									.append("' \n\r");
							flag = jdbcManager.executeSQL(conn, sqlComment.toString(), obj);
						}
					}
				}
			}
		}
			break;
		case Constant.SOURCE_TYPE_MYSQL: {
			if (sourceType == Constant.SOURCE_TYPE_ORACLE) {
				sql.append("CREATE TABLE `").append(manageTableDO.getTargetTableName()).append("` (") // 表名
						.append("`AUTO_ID` INT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',");
				for (ManageColumnsVO i : manageColumnsVOList) {
					j++;
					sql.append("`").append(i.getColumnName()).append("` ") // 字段名
							.append(CTConversionUtils.O2M(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale()))
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL" : " NOT NULL ")) // 是否为空
							.append(((i.getColumnComment() != null && !"".equals(i.getColumnComment()))
									? (" COMMENT '" + i.getColumnComment() + "'")
									: " "))
							.append((j < manageColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ENGINE=InnoDB COMMENT='").append(manageTableDO.getTargetTableComment()).append("';"); // 表注释
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
			}
			if (sourceType == Constant.SOURCE_TYPE_MYSQL) {
				sql.append("CREATE TABLE `").append(manageTableDO.getTargetTableName()).append("` (") // 表名
						.append("`AUTO_ID` INT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT  COMMENT '自增ID',");
				for (ManageColumnsVO i : manageColumnsVOList) {
					j++;
					sql.append("`").append(i.getColumnName()).append("` ") // 字段名
							.append(CTConversionUtils.Complete(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale()))
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL " : " NOT NULL ")) // 是否为空
							.append(((i.getColumnComment() != null && !"".equals(i.getColumnComment()))
									? (" COMMENT '" + i.getColumnComment() + "'")
									: " "))
							.append((j < manageColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ENGINE=InnoDB COMMENT='").append(manageTableDO.getTargetTableComment()).append("';"); // 表注释
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
			}
		}
			break;
		default:
			break;
		}
		logger.info(sql.toString());
		return flag;
	}

}
